/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package no.ffi.fca.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.security.auth.x500.X500Principal;
import no.ffi.fca.Settings;
import no.ffi.fca.db.result.CertificateList;
import no.ffi.fca.db.result.CertificateList.Certificate;
import no.ffi.fca.db.result.UserList;

/**
 *
 * @author stian
 */
public class DBHelper {
    Connection connection;

    public DBHelper() throws DBHelperException {
        connection = null;
        try
        {
            Class.forName("org.sqlite.JDBC");
            String connString = "jdbc:sqlite:" + Settings.PATH_DATABASE;
            connection = DriverManager.getConnection(connString);
        }
        catch(SQLException e)
        {
            throw new DBHelperException("Could not get database connection: " + e);
        }
        catch(ClassNotFoundException e) {
            throw new DBHelperException(e);
        }
    }

    public void close() {
        try {
            if(connection != null)
                connection.close();
        }
        catch (Exception e) {
            System.err.println("SQLite error destructing DBHelper: " + e);
        }
    }
    
    public CertificateList getCertificateList() {
        return getCertificateList(Integer.MIN_VALUE);
    }
    
    public CertificateList getCertificateList(int uid) {
        PreparedStatement statement;
        try {
            if (uid == Integer.MIN_VALUE) {
                statement = connection.prepareStatement(
                    "SELECT * FROM Certificates ORDER BY Id ASC;",
                    Statement.RETURN_GENERATED_KEYS);
            }
            else {
                statement = connection.prepareStatement(
                    "SELECT * FROM Certificates WHERE Uid=? ORDER BY Id ASC;",
                    Statement.RETURN_GENERATED_KEYS);
                statement.setInt(1, uid);
            }
            ResultSet rs = statement.executeQuery();
            CertificateList cl = new CertificateList();
            while (rs.next()) {
                cl.add(
                        rs.getInt(1), 
                        rs.getInt(2), 
                        rs.getString(3),
                        rs.getString(4), 
                        rs.getString(5), 
                        rs.getString(6), 
                        rs.getString(7), 
                        rs.getLong(8),
                        rs.getLong(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getString(12));
            }
            return cl;
        }
        catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }
    
    public Certificate getCertificate(X500Principal dn) {
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement("SELECT * FROM Certificates WHERE Subject=?;");
            statement.setString(1, dn.getName());
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                Certificate ret = new Certificate(
                        rs.getInt(1), 
                        rs.getInt(2), 
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5), 
                        rs.getString(6), 
                        rs.getString(7), 
                        rs.getLong(8),
                        rs.getLong(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getString(12));
                return ret;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        return null;
    }
    
        public Certificate getCertificate(String serial, String ticket) {
        PreparedStatement statement;
        try {
            System.out.println("s:" + serial + " t:" + ticket);
            statement = connection.prepareStatement("SELECT * FROM Certificates WHERE Serial=? AND Ticket=?;");
            statement.setString(1, serial);
            statement.setString(2, ticket);
            ResultSet rs = statement.executeQuery();
            System.out.println("rsts:" + rs.toString());
            if (rs.next()) {
                Certificate ret = new Certificate(
                        rs.getInt(1), 
                        rs.getInt(2), 
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5), 
                        rs.getString(6), 
                        rs.getString(7), 
                        rs.getLong(8),
                        rs.getLong(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getString(12));
                return ret;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        return null;
    }
    
    public Certificate getCertificate(String serial) {
        serial = serial.toUpperCase();
        if (serial.length() % 2 == 1) serial = "0" + serial;
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement("SELECT * FROM Certificates WHERE Serial=?;");
            statement.setString(1, serial);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                Certificate ret = new Certificate(
                        rs.getInt(1), 
                        rs.getInt(2), 
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5), 
                        rs.getString(6), 
                        rs.getString(7), 
                        rs.getLong(8),
                        rs.getLong(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getString(12));
                return ret;
            }
        } catch (SQLException e) {
            System.err.println(e);
        }
        return null;
    }
    
    public Certificate getAIKCertificate(String serialNumber) {
        if (serialNumber.length() % 2 == 1) serialNumber = "0" + serialNumber;
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement("SELECT * FROM AIKCertificates WHERE Token=?;");
            statement.setString(1, serialNumber);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                // Certificate: id   uid    name    subject     hash    hash_old    serial      nvb     nva     type    revoked     ticket
                // Result:      Id   null   null    Subject     null    Hash_old    Serial      null    null    null    null        Token 
                // Index:       1    -      -       2           -       3           4           -       -       -       -           5
                Certificate ret = new Certificate(
                        rs.getInt(1), 
                        -1, 
                        null,
                        rs.getString(2),
                        null, 
                        rs.getString(3), 
                        rs.getString(4), 
                        0,
                        0,
                        null,
                        0,
                        rs.getString(5));
                return ret;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        return null;
    }

    
    public Certificate getCertificate(int cid) {
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement("SELECT * FROM Certificates WHERE Id=?;");
            statement.setInt(1, cid);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                Certificate ret = new Certificate(
                        rs.getInt(1), 
                        rs.getInt(2), 
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5), 
                        rs.getString(6), 
                        rs.getString(7), 
                        rs.getLong(8),
                        rs.getLong(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getString(12));
                return ret;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        return null;
    }
    
    public UserList getUserList() {
        try {
            UserList us = new UserList();
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);
            ResultSet rs = statement.executeQuery("SELECT * FROM Users");
            while (rs.next()) {
                us.add(rs.getInt(1), rs.getString(2), rs.getString(3));
            }
            return us;
        } catch (SQLException e) {
            System.out.println(e);
        }
        return null;
    }
    
    public int newCertificate(
            int uid, 
            String name,
            String subject, 
            String hash, 
            String hash_old, 
            String serial, 
            long nvb, 
            long nva,
            String type,
            String ticket) throws DBHelperException {
        // uid , subject, hash, hash_old, serial, nvb, nva
        int affectedRows;
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(
                "INSERT INTO Certificates VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", 
                Statement.RETURN_GENERATED_KEYS);
            statement.setInt(1, uid);
            statement.setString(2, name);
            statement.setString(3, subject);
            statement.setString(4, hash);
            statement.setString(5, hash_old);
            statement.setString(6, serial);
            statement.setLong(7, nvb);
            statement.setLong(8, nva);
            statement.setString(9, type);
            statement.setInt(10, 0);
            statement.setString(11, ticket);
            affectedRows = statement.executeUpdate();
        } catch (SQLException e) {
            throw new DBHelperException(e);
        }
        if (affectedRows < 1) {
            throw new DBHelperException("executeUpdate() did not insert any rows");
        }
        try {
            ResultSet generatedKeys = statement.getGeneratedKeys();
            if (generatedKeys.next()) {
                return generatedKeys.getInt(1);
            }
            throw new DBHelperException("could not get generated key");
        }
        catch (SQLException e) {
            throw new DBHelperException("could not get generated key");
        }
    }

    public void revokeCertificate(int cid) throws DBHelperException {
        int affectedRows = 0;
        try {
            PreparedStatement statement = connection.prepareStatement(
                "UPDATE Certificates SET Revoked=1 WHERE Id=?;", 
                Statement.RETURN_GENERATED_KEYS);
            statement.setInt(1, cid);
            affectedRows = statement.executeUpdate();
        } catch (SQLException e) {
            throw new DBHelperException(e);
        }
        if (affectedRows < 1) {
            throw new DBHelperException("executeUpdate() did not update any rows");
        }
    }
    
    public void deleteCertificate(int cid) throws DBHelperException {
        int affectedRows;
        try {
            PreparedStatement statement = connection.prepareStatement(
                "DELETE FROM Certificates WHERE Id=?;", 
                Statement.RETURN_GENERATED_KEYS);
            statement.setInt(1, cid);
            affectedRows = statement.executeUpdate();
        } catch (SQLException e) {
            throw new DBHelperException(e);
        }
        if (affectedRows < 1) {
            throw new DBHelperException("executeUpdate() did not delete any rows");
        }    
    }

    public int newAIKCertificate(String subject, String hash_old, String serial, String token) throws DBHelperException {
        int affectedRows;
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(
                "INSERT INTO AIKCertificates VALUES(NULL, ?, ?, ?, ?);", 
                Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, subject);
            statement.setString(2, hash_old);
            statement.setString(3, serial);
            statement.setString(4, token);
            affectedRows = statement.executeUpdate();
        } catch (SQLException e) {
            throw new DBHelperException(e);
        }
        if (affectedRows < 1) {
            throw new DBHelperException("executeUpdate() did not insert any rows");
        }
        try {
            ResultSet generatedKeys = statement.getGeneratedKeys();
            if (generatedKeys.next()) {
                return generatedKeys.getInt(1);
            }
            throw new DBHelperException("could not get generated key");
        }
        catch (SQLException e) {
            throw new DBHelperException("could not get generated key");
        }
    }

    public boolean createDatabase() {
        try {
            Statement stat = connection.createStatement();
            stat.executeUpdate("CREATE TABLE AIKCertificates(Id integer primary key, Subject text, Hash_old text, Serial text, Token text);");
            stat.executeUpdate("CREATE TABLE Certificates(Id integer primary key, Uid integer, Name text, Subject text, Hash text, Hash_old text, Serial text, NVB integer, NVA integer, Type text, Revoked integer, Ticket text);");
            stat.executeUpdate("CREATE TABLE Users(Id integer primary key, Name text, Email Text);");
        } catch (SQLException e) {
            System.out.println("Database error: " + e);
            return false;
        }
        return true;
    }

    public int newUser(String name, String email) throws DBHelperException {
        int affectedRows;
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(
                "INSERT INTO Users VALUES(NULL, ?, ?);", 
                Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, name);
            statement.setString(2, email);
            affectedRows = statement.executeUpdate();
        } catch (SQLException e) {
            throw new DBHelperException(e);
        }
        if (affectedRows < 1) {
            throw new DBHelperException("executeUpdate() did not insert any rows");
        }
        try {
            ResultSet generatedKeys = statement.getGeneratedKeys();
            if (generatedKeys.next()) {
                return generatedKeys.getInt(1);
            }
            throw new DBHelperException("could not get generated key");
        }
        catch (SQLException e) {
            throw new DBHelperException("could not get generated key");
        }
    }

    public static class DBHelperException extends SQLException {
        
        public DBHelperException(String s) {
            super(s);
        }
        
        public DBHelperException(Throwable t) {
            super(t);
        }
        
    }
}